/*
 * Created on 03-Oct-2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package wstrader.task.traffic.di;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import pc.sql.value.*;
import pc.sql.value.StringValue;
import pc.system.*;
import pc.util.Utils;
import wstrader.task.GenReport;
import wstrader.task.trade.TradeTask;

/**
 * @author NANSA
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Instruction extends TradeTask
{
	  public long lsRec=0;
	  public Instruction(Tasks owner)
	  {
	    super(owner);
	  }	
	  
	  public long doTask()
	  {
	    String table_name = "delivery_instruction";
	    int task_id     = this.getRequest().getIntValue("task_id");
	    int Status      = this.getRequest().getIntValue("status");
	    long focused_id = this.getRequest().getLongValue("delivery_instruction");
	    switch (task_id)
		{
	      case 1 : // Update
	      	focused_id = doUpdate(table_name,focused_id);
	      	updatePendingReceiveField(focused_id);
	      	//if (Status ==1) updateStockDetails(focused_id);
	      	lsRec=focused_id;
	        break;
	      case 2 : // Delete
	    	float chkqty = this.getSum("net_weight", "inst_id", "weight_note",focused_id); 
	    	if (chkqty <=0)
	    	{
	    		focused_id = doDelete(table_name,focused_id);
	    		updatePendingReceiveField(focused_id);
	    	}
	        lsRec=focused_id = 1;
	        break;
	      case 3 : // Update
		      	focused_id = updateWeightNotes(focused_id);
		      	updatePendingReceiveField(focused_id);
		      	//if (Status ==1) updateStockDetails(focused_id);
		      	lsRec=focused_id;
		        break;	        
	      default : // View only
	        break;
	    }
	    this.setFocusedID(focused_id);

	    return focused_id;
	  }  
	  
	  public long updateWeightNotes(long delvyInsId)
	  {
		long WgtId = getRequest().getLongValue("wgt_id");
      	String WNRef = getRequest().getStringValue("ref_number_w");//this.getNew_WNRef();
      	String Type = "I";
      	java.sql.Date DelvyDt = Utils.strToDate(getRequest().getStringValue("delvy_date"));
      	int GradeId = getRequest().getIntValue("grade_id");
      	float GrossWgt = getRequest().getFloatValue("net_weight");
      	float TareWgt = 0;
      	float NetWgt = getRequest().getFloatValue("net_weight");
      	float NoOfBags = getRequest().getFloatValue("no_of_bags_delvy");
      	float StkWgt = getRequest().getFloatValue("net_weight");
      	float StkBag = getRequest().getFloatValue("no_of_bags_delvy");
      	long RecExist = this.findID("weight_note","id",WgtId);
     
      	String SqlStr="";
      	if (RecExist > 0)
      	{
     		SqlStr ="update weight_note " +
    		"SET ref_number = ?, " +
    		"date = ?, " +
    		"type = ?, " +
    		"grade_id = ?, " +
    		"gross_weight = ?, " +
    		"tare_weight = ?, " +
    		"net_weight = ?, " +
    		"no_of_bags = ?, " +
    		"stock_weight = ?, " +
    		"stock_bags = ?, " +
    		"inst_id = ? " +
    		"where id = ?";	
      		Vector vp = new Vector();
      		vp.addElement(new StringValue(WNRef));
      		vp.addElement(new DateValue(DelvyDt));
      		vp.addElement(new StringValue(Type));
      		vp.addElement(new IntValue(GradeId));
      		vp.addElement(new FloatValue(GrossWgt));
      		vp.addElement(new FloatValue(TareWgt));
      		vp.addElement(new FloatValue(NetWgt));
      		vp.addElement(new FloatValue(NoOfBags));
      		vp.addElement(new FloatValue(StkWgt));
      		vp.addElement(new FloatValue(StkBag));
      		vp.addElement(new LongValue(delvyInsId));      		
      		vp.addElement(new LongValue(WgtId));
      		int ret = getSQLCommand().executeUpdate(SqlStr,vp);	  		
      	}
      	else
      	{
      		SqlStr ="insert weight_note " +
      	    		"SET ref_number = ?, " +
      	    		"date = ?, " +
      	    		"type = ?, " +
      	    		"grade_id = ?, " +
      	    		"gross_weight = ?, " +
      	    		"tare_weight = ?, " +
      	    		"net_weight = ?, " +
      	    		"no_of_bags = ?, " +
      	    		"stock_weight = ?, " +
      	    		"stock_bags = ?, " +
      	    		"inst_id = ?";
      			Vector vp = new Vector();
          		vp.addElement(new StringValue(WNRef));
          		vp.addElement(new DateValue(DelvyDt));
          		vp.addElement(new StringValue(Type));
          		vp.addElement(new IntValue(GradeId));
          		vp.addElement(new FloatValue(GrossWgt));
          		vp.addElement(new FloatValue(TareWgt));
          		vp.addElement(new FloatValue(NetWgt));
          		vp.addElement(new FloatValue(NoOfBags));
          		vp.addElement(new FloatValue(StkWgt));
          		vp.addElement(new FloatValue(StkBag));
          		vp.addElement(new LongValue(delvyInsId));
      			int ret = getSQLCommand().executeUpdate(SqlStr,vp);	  		
      	}		  
		  return delvyInsId;
	  }
	  
	  public ResultSet fillDelvyInsListBox()
	  {
	  	int CntId = getRequest().getIntValue("contracts");
	  	String Sql ="";
	  	if (CntId == 0)
	  	{
	  		Sql ="select id,ref_number from delivery_instruction";
	  	}
	  	else
	  	{
	  		//Sql= "select id,ref_number from delivery_instruction where contract_id = " + CntId;
	  		Sql= "select id,ref_number from delivery_instruction";
	  	}
	  	return getSQLCommand().executeQuery(Sql);
	  }
	  
	  public ResultSet getDeliveryInstDetails()
	  {
	  	String sql ="";
	  	long id = getRequest().getIntValue("delivery_instruction");
	  	if (id <= 0)
	  	{
	  		id=lsRec;
	  		System.out.println("Id:-"+id);
	  	}
	  	
	  	int CntId = getRequest().getIntValue("contracts");
		
	  	if (id > 0)
	  	{
	  	sql = "SELECT contracts.contract_no,contracts.ref_number as cnt_ref_number,contracts.supplier_buyer_id, " +
			  "contracts.supplier_buyer_ref,contracts.quality_id,contracts.origin_id, " +
              "contracts.grade_id,contracts.packing_id,contracts.tons as cnttons, " +
			  "delivery_instruction.*,delivery_instruction.tons as tons " +
			  "FROM delivery_instruction " +
			  "INNER JOIN contracts ON (delivery_instruction.contract_id=contracts.id) " +
			  "WHERE delivery_instruction.id = " + id;
	  	}
	  	else
	  	{
	  	sql = "select 0 as id,id as contract_id,ref_number as cnt_ref_number,contract_no, " +
		      "origin_id, quality_id, grade_id ,warehouse_id,tons as cnttons, " +
		      "supplier_buyer_id,supplier_buyer_ref,packing_id,pending_tons as tons, " +
			  "kg_per_bag, (pending_tons*1000)/kg_per_bag as no_of_bags from contracts " +
			  "where id = " + CntId;
	  	}
	  	return getSQLCommand().executeQuery(sql);
	  }
	  
	  public ResultSet getDI_List()
	  {
	  	String sql = "";
	  	int CntId = getRequest().getIntValue("contracts");
	  	if (CntId > 0)
	  	{
	  		sql = "select id,ref_number as name,status from delivery_instruction where id = " + CntId + " order by ref_number desc";	
	  	}
	  	else
	  	{
	  		sql = "select id,ref_number as name,status from delivery_instruction order by ref_number desc";
	  	}
	  	return getSQLCommand().executeQuery(sql);
	  }
	  
	  public ResultSet getDI_ListView()
	  {
		 int CntId = getRequest().getIntValue("contracts");
		 int BuyerId = getRequest().getIntValue("supplier_buyer_id");
		 int OriginId = getRequest().getIntValue("origin_id");
		 int QualityId = getRequest().getIntValue("quality_id");
		 int GradeId = getRequest().getIntValue("grade_id");
		 int Status = getRequest().getIntValue("status");
	  	java.sql.Date LsDt = getRequest().getValueDate("ls_date");		
	  	
	  	String sql = "";
	  	sql = "SELECT delivery_instruction.*,sum(weight_note.net_weight) as recdton,sum(weight_note.no_of_bags) as recdbag, " +
		"grade_master.name,grade_master.origin_id,grade_master.quality_id,grade_master.grade_code  " +
		"FROM delivery_instruction " +
		"INNER JOIN contracts ON (delivery_instruction.contract_id=contracts.id) " +
		"INNER JOIN weight_note ON (delivery_instruction.id=weight_note.inst_id) " +
		"INNER JOIN grade_master ON (delivery_instruction.grade_id=grade_master.id) " +
		"WHERE (weight_note.type = ?) " +
		"and (contract_id = ? OR ?) " +			
		"and (supplier_id = ? OR ?) " +
		"and (grade_master.origin_id = ? OR ?) " +
		"and (grade_master.quality_id = ? OR ?) " +
		"and (grade_master.id = ? OR ?) " +
		"and (delivery_instruction.status = ? OR ?) " +
		"and ((last_date < ? OR ?) OR (last_date IS NULL)) " +
		"GROUP BY `weight_note`.`inst_id`";
	  	
	    Vector vp = new Vector();

	    vp.addElement(new StringValue("I"));
	    
	    vp.addElement(new IntValue(CntId));
	    vp.addElement(new BooleanValue(CntId == 0));

	    vp.addElement(new IntValue(BuyerId));
	    vp.addElement(new BooleanValue(BuyerId == 0));

	    vp.addElement(new IntValue(OriginId));
	    vp.addElement(new BooleanValue(OriginId == 0));
	  	
	    vp.addElement(new IntValue(QualityId));
	    vp.addElement(new BooleanValue(QualityId == 0));
	    
	    vp.addElement(new IntValue(GradeId));
	    vp.addElement(new BooleanValue(GradeId == 0));
	    
	    vp.addElement(new IntValue(Status));
	    vp.addElement(new BooleanValue(Status == 3));
	    
  		vp.addElement(new DateValue(LsDt));
  		vp.addElement(new BooleanValue(LsDt.compareTo(new java.sql.Date(0)) == 0));
  		
	  	/*sql = "SELECT delivery_instruction.*, " +
		"grade_master.name,grade_master.origin_id,grade_master.quality_id,grade_master.grade_code  " +
		"FROM delivery_instruction " +
		"INNER JOIN grade_master ON (delivery_instruction.grade_id=grade_master.id)";*/	  	

	  	return getSQLCommand().executeQuery(sql,vp);
	  }	  

	  public ResultSet getContract_List()
	  {
	  	return getSQLCommand().executeQuery("select id,ref_number as name from contracts where type='P'");
	  }
	  
	  public ResultSet getBroker_List()
	  {
	  	return getSQLCommand().executeQuery("select id,contract_no as name from contracts where type='P'");
	  }	  
	  
	  public String getNew_DI()
	  {
		int dy = Utils.getCurYear() % 100;
		String yrs = Utils.formatNumber(dy,2);
	  	String No="";
	    String sql =
	        "SELECT MAX(ref_number) as ref_number FROM delivery_instruction ";
	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));

	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "SWCVN-DI-"+yrs+"-00000";
	    String sub = "";
	    rc.close();

	    if (ref=="") ref="SWCVN-DI-"+yrs+"-00000";
	    int RefNo=Utils.strToInt(ref.substring(12,17))+1;
	    
	    if (Utils.numberToStr(RefNo,0,false).length()==5) ref = Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==4) ref = "0" + Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==3) ref = "00" + Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==2) ref = "000" + Utils.numberToStr(RefNo,0,false);
	    if (Utils.numberToStr(RefNo,0,false).length()==1) ref = "0000" + Utils.numberToStr(RefNo,0,false);
	    
	    No = "SWCVN-DI-"+yrs + "-" + ref;//this.getNextRefNumber(ref);
	    return No;
	  }
	  
	  
	  public String getNew_DIRef()
	  {
	    String sql = "SELECT MAX(ref_number) as ref_number FROM delivery_instruction";
	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));
	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "WS-DI 00-00000";
	    System.out.println("DI-No---- "+ref);
	    rc.close();
	    return "WS-DI" + " " + this.getNextRefNumber(ref);
	  }
	  
	  public String getNew_WNRef()
	  {
	    String sql = "SELECT MAX(ref_number) as ref_number FROM weight_note";
	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));
	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "WS-WN 00-00000";
	    System.out.println("WN-No---- "+ref);
	    rc.close();
	    return "WS-WN" + " " + this.getNextRefNumber(ref);
	  }
	  
	  public ResultSet fetchDeliveryList()
	  {
	  	int DelvyInsId = getRequest().getIntValue("delivery_instruction");
	  	String Sql = "Select * from weight_note where inst_id= " + DelvyInsId + " order by id";
	  	return getSQLCommand().executeQuery(Sql);
	  }
}

 

class Report extends GenReport
{
  class Total {
    private float tons = 0;
    private float delivered_tons = 0;
    private float pending_tons = 0;

    Total()
    {

    }

    Total(ResultSet rs)
    {
      try {
        this.tons = rs.getFloat("tons");
        this.delivered_tons = rs.getFloat("delivered_tons");
        this.pending_tons = this.tons - this.delivered_tons;
      } catch (SQLException e) {
      }
    }

    private void add(Total total)
    {
      this.tons += total.tons;
      this.delivered_tons += total.delivered_tons;
      this.pending_tons = this.tons - this.delivered_tons;
    }
  }

  private int row = 0;
  private int index = 0;
  private ResultSet rs = null;

  private java.sql.Date report_date_from = null;
  private java.sql.Date report_date_to = null;

  public Report(Instruction owner)
  {
    super(owner,"traffic.report.DI.xls");
    this.report_date_from = Utils.strToDate(this.getRequest().getStringValue("report_date_from"));
    this.report_date_to   = Utils.strToDate(this.getRequest().getStringValue("report_date_to"));
  }

  private ResultSet getDI_Detail()
  {
    int contract_id = getRequest().getIntValue("contract_id");
    int contract_no = getRequest().getIntValue("contract_no");
    int supplier_id = getRequest().getIntValue("supplier_id");
    int origin_id   = getRequest().getIntValue("origin_id");
    int quality_id  = getRequest().getIntValue("quality_id");
    int grade_id    = getRequest().getIntValue("grade_id");

    String sql =
        "SELECT ct.ref_number as contract_ref_number," +
        "       ct.contract_no," +
        "       su.name as supplier," +
        "       og.name as origin," +
        "       qu.name as quality," +
        "       gr.name as grade, " +
        "       pk.name as packing, " +
        "       di.* " +
        "FROM delivery_instruction di " +
        "LEFT JOIN contracts      ct ON di.contract_id = ct.id " +
        "LEFT JOIN company_master su ON ct.supplier_buyer_id = su.id " +
        "LEFT JOIN packing_master pk ON di.packing_id  = pk.id " +
        "LEFT JOIN origin_master  og ON ct.origin_id   = og.id " +
        "LEFT JOIN quality_master qu ON ct.quality_id  = qu.id " +
        "LEFT JOIN grade_master   gr ON ct.grade_id    = gr.id " +
        "WHERE (di.contract_id = ? OR ?) " +
        "  AND (ct.contract_no = ? OR ?) " +
        "  AND (di.date >= ? OR ?) " +
        "  AND (di.date <= ? OR ?) " +
        "  AND (ct.supplier_buyer_id = ? OR ?) " +
        "  AND (ct.origin_id = ? OR ?) " +
        "  AND (ct.quality_id  = ? OR ?) " +
        "  AND (ct.grade_id    = ? OR ?) " +
        "ORDER BY di.status, di.ref_number ";

    Vector vp = new Vector();

    vp.addElement(new IntValue(contract_id));
    vp.addElement(new BooleanValue(contract_id == 0));

    vp.addElement(new IntValue(contract_no));
    vp.addElement(new BooleanValue(contract_no == 0));

    vp.addElement(new DateValue(this.report_date_from));
    vp.addElement(new BooleanValue(this.report_date_from == null));

    vp.addElement(new DateValue(this.report_date_to));
    vp.addElement(new BooleanValue(this.report_date_to == null));

    vp.addElement(new IntValue(supplier_id));
    vp.addElement(new BooleanValue(supplier_id == 0));

    vp.addElement(new IntValue(origin_id));
    vp.addElement(new BooleanValue(origin_id == 0));

    vp.addElement(new IntValue(quality_id));
    vp.addElement(new BooleanValue(quality_id == 0));

    vp.addElement(new IntValue(grade_id));
    vp.addElement(new BooleanValue(grade_id == 0));

    return getSQLCommand().executeQuery(sql,vp);
  }

  private Total oneRow()
  {
    Total total = new Total(rs);
    int col = 0;
    try {
      this.setCellValue(col++,row,++index);
      this.setCellValue(col++, row, rs.getString("ref_number"));
      this.setCellValue(col++,row,rs.getDate("date"));

      this.setCellValue(col++,row,rs.getDate("from_date"));
      this.setCellValue(col++,row,rs.getDate("to_date"));

      this.setCellValue(col++,row,rs.getString("contract_ref_number"));
      this.setCellValue(col++,row,rs.getString("contract_no"));

      this.setCellValue(col++,row,rs.getString("supplier"));

      this.setCellValue(col++,row,rs.getString("origin"));
      this.setCellValue(col++,row,rs.getString("quality"));
      this.setCellValue(col++,row,rs.getString("grade"));
      this.setCellValue(col++,row,rs.getString("packing"));

      this.setCellValue(col++,row,total.tons);
      this.setCellValue(col++,row,total.delivered_tons);
      this.setCellValue(col++,row,total.pending_tons);
    }  catch (SQLException e) {
      e.printStackTrace();
    }
    return total;
  }

  public String generate()
  {
    rs = this.getDI_Detail();
    row = this.getFirstRow();
    Total total = new Total();
    this.set_CellValue(2,0,this.report_date_from);
    this.set_CellValue(2,1,this.report_date_to);

    try {
      while (rs.next()) {
        total.add(oneRow());
        row++;
        this.shiftRows(row,row,1);
      }
    }  catch (SQLException e) {
      System.out.println(e.getMessage());
    }

    this.shiftRows(row+1,row+1,-1);
    int x = 12;
    this.set_CellValue(x++,row,total.tons);
    this.set_CellValue(x++,row,total.delivered_tons);
    this.set_CellValue(x++,row,total.pending_tons);

    getSQLCommand().close(rs);
    return this.export();
  }

}
